package com.fi.common.domain.vo;


import cn.hutool.core.util.StrUtil;
import com.fi.recogn.dto.table.Table;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.ToString;
import lombok.experimental.Accessors;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.*;
import java.util.stream.Collectors;

import static com.fi.recogn.enums.ExcelOcrTypeEnum.TABLE_WITH_LINE;

/**
 * @Description TODO excel 元数据信息
 * @Author lujiahong
 * @Date 2024/8/12 17:50
 */
@Slf4j
@Data
@Accessors(chain = true)
@EqualsAndHashCode(callSuper = true)
@ToString(callSuper = true)
public class FileDataExcel extends FileData{

    private Workbook workbook;// excel 工作簿
    private StringBuilder contents;// excel 内容
    private List<Table> tables;// excel 表格信息
    private final static DataFormatter dataFormatter = new DataFormatter();

    public void setWorkbook(Workbook workbook) {
        this.workbook = workbook;
        this.initContent();
    }

    private void initContent() {
        this.contents = new StringBuilder();
        this.tables = new ArrayList<>();
        Table table = Table.builder()
                .type(TABLE_WITH_LINE.type())
                .pageNumber(1)
                .manager(super.getManager())
                .accessId(super.getId())
                .build();
        List<com.fi.recogn.dto.table.Cell> tableCells = new LinkedList<>();
        int numberOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            for (Row row : sheet) {
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    int rowIndex = cell.getRowIndex();
                    int colIndex = cell.getColumnIndex();
                    int startRow = rowIndex;
                    int endRow = rowIndex;
                    int startCol = colIndex;
                    int endCol = colIndex;
                    // 检查单元格是否被合并
                    boolean isMerged = false;
                    for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                        CellRangeAddress mergedRegion = sheet.getMergedRegion(j);
                        if (mergedRegion.isInRange(rowIndex, colIndex)) {
                            log.debug(" (Merged: " + mergedRegion.formatAsString() + ")");
                            isMerged = true;
                            startRow = mergedRegion.getFirstRow();
                            endRow = mergedRegion.getLastRow();
                            startCol = mergedRegion.getFirstColumn();
                            endCol = mergedRegion.getLastColumn();
                            break;
                        }
                    }
                    if (!isMerged) {
                        log.debug(" (Not Merged)");
                    }
                    // 将单元格内容作为STRING类型提取
                    String cellValue = StrUtil.cleanBlank(getCellValueAsString(cell));
//                    if(!isMerged || StrUtil.isNotBlank(cellValue)){
                        log.info("Cell (" + rowIndex + ", " + colIndex + ") - cellText ("+cellValue+") - Custom Coordinates: (" + startRow + ", " + endRow + ", " + startCol + ", " + endCol + ")");
                        this.contents.append(cellValue).append("\t");
                        tableCells.add(com.fi.recogn.dto.table.Cell.builder()
                                        .isHeader(0)
                                        .startRow(startRow)
                                        .endRow(endRow)
                                        .startCol(startCol)
                                        .endCol(endCol)
                                        .text(cellValue)
                                        .build());
//                    }
                }
                this.contents.append("\n");
            }
        }
        table.setTableCells(finalTableCells(tableCells));
        this.tables.add(table);
        super.setContent(this.contents.toString());
        super.setOcrPages(Collections.singletonMap(1, this.tables));
    }

    private List<com.fi.recogn.dto.table.Cell> finalTableCells(List<com.fi.recogn.dto.table.Cell> tableCells) {
        // 去重后有相同坐标的保留值不为空的，无相同坐标的值为空也要保留一个
        tableCells =  tableCells.stream().distinct().collect(Collectors.toList());
        Map<String, com.fi.recogn.dto.table.Cell> cellMap = new LinkedHashMap<>();
        for (com.fi.recogn.dto.table.Cell tableCell : tableCells) {
            String key = tableCell.getStartRow() + "-" + tableCell.getEndRow()+ "-" + tableCell.getStartCol()+ "-" + tableCell.getEndCol();
            if (cellMap.containsKey(key)) {
                com.fi.recogn.dto.table.Cell existCell = cellMap.get(key);
                if (StrUtil.isBlank(existCell.getText())) {
                    cellMap.put(key, tableCell);
                }
            }else {
                cellMap.put(key, tableCell);
            }
        }
        return new ArrayList<>(cellMap.values());
    }

    private static String getCellValueAsString(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return dataFormatter.formatCellValue(cell);
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }
}